package com.baomidou.springwind.excel;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.springwind.entity.*;
import com.baomidou.springwind.enums.NodeTypeEnum;
import com.baomidou.springwind.mapper.NetworkLngLatDao;
import com.baomidou.springwind.service.NetworkPlanningService;
//import com.sun.xml.internal.ws.api.pipe.FiberContextSwitchInterceptor;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * excel文件读取
 *
 * @author luox
 */
@Component
public class NetworkPlanningDataReader extends ExcelReader<NetworkPlanning> {

    @Autowired
    private NetworkPlanningService networkPlanningService;
    @Autowired
    private NetworkLngLatDao networkLngLatDao;
    @Override
    protected String saveData(List<NetworkPlanning> dataList, Long batchId) {
        List<NetworkPlanning> dataList1=new ArrayList<NetworkPlanning>();
        List<NetworkPlanning> dataList2=new ArrayList<NetworkPlanning>();
        for (int i=0;i<dataList.size();i++){
            if (dataList.get(i).getType()==1){
                dataList1.add(dataList.get(i));
            }else if(dataList.get(i).getType()==2){
                dataList2.add(dataList.get(i));
            }
        }
        EntityWrapper<NetworkPlanning> wrapper = new EntityWrapper<NetworkPlanning>();
        wrapper.eq("batch_id", batchId);
        deleteFile(batchId);
        networkPlanningService.delete(wrapper);//删除之前导入的数据
        //一级配送中心
        for (NetworkPlanning networkPlanning : dataList1) {
            networkPlanning.setBatchId(batchId);
            networkPlanning.setOperatorId(getCurrentUserId());
            networkPlanning.setCreateTime(new Date());
            networkPlanning.setpId(0L);
        }
        networkPlanningService.insertBatch(dataList1, 100);
        //二级配送中心
        EntityWrapper<NetworkPlanning> wrapper1;
        for (NetworkPlanning networkPlanning : dataList2) {
            networkPlanning.setBatchId(batchId);
            wrapper1 = new EntityWrapper<NetworkPlanning>();
            wrapper1.eq("batch_id", batchId);
            wrapper1.eq("name",networkPlanning.getBatchName());
            wrapper1.eq("type",1);
            final List<NetworkPlanning> networkPlanningList = networkPlanningService.selectList(wrapper1);
            networkPlanning.setpId(networkPlanningList.get(0).getId());
            networkPlanning.setOperatorId(getCurrentUserId());
            networkPlanning.setCreateTime(new Date());
        }
        networkPlanningService.insertBatch(dataList2, 100);
        return "";
    }

    private void deleteFile(Long batchId) {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        String path = request.getSession().getServletContext().getRealPath("");
        path = path.substring(0, path.lastIndexOf("\\"));
        path = path.substring(0, path.lastIndexOf("\\"));
        File file = new File(path + "/temp/route/" + batchId + ".obj");
        file.delete();
    }

    @Override
    protected List<NetworkPlanning> verifyData(HSSFWorkbook workbook) {
        List<NetworkPlanning> networkPlanningList = new ArrayList<NetworkPlanning>();
        networkPlanningList.addAll(primaryDelivery(workbook));
        networkPlanningList.addAll(secondaryDelivery(workbook));
        return networkPlanningList;
    }
    @Override
    protected List<NetworkPlanning> verifyData7(XSSFWorkbook workbook) {
        return null;
    }

    //获取一级配送中心的sheet
    private List<NetworkPlanning> primaryDelivery(HSSFWorkbook workbook) {
        List<NetworkPlanning> networkPlanningList = new ArrayList<NetworkPlanning>();
        HSSFSheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        NetworkPlanning networkPlanning;
        for (int i = 1; i <= lastRowNum; i++) {
            sheet.getRow(i);
            networkPlanning = new NetworkPlanning();
            //如果获取到了，则设置对应的经纬度
            if(getStringCellValue(sheet.getRow(i), 1)==null||getStringCellValue(sheet.getRow(i), 1)==""||getStringCellValue(sheet.getRow(i), 2)==null||getStringCellValue(sheet.getRow(i), 2)==""){
                //调用数据库获取到对应的经纬度
                EntityWrapper<NetworkLngLat> wrapperBranch = new EntityWrapper<NetworkLngLat>();
                wrapperBranch.eq("name", getStringCellValue(sheet.getRow(i), 0));
                List<Map<String, Object>> networkLngLatBranch=networkLngLatDao.selectMaps(wrapperBranch);
                if (networkLngLatBranch.size()>0){
                    networkPlanning.setCoordinateX(new BigDecimal(networkLngLatBranch.get(0).get("lng").toString()));
                    networkPlanning.setCoordinateY(new BigDecimal(networkLngLatBranch.get(0).get("lat").toString()));
                }else{
                    //如果为获取到，调用百度地图，将该地址保存到数据库networkLngLatbran
                    JSONArray jsonArray=latlng(getStringCellValue(sheet.getRow(i), 0));
                    NetworkLngLat networkLngLatbran=new NetworkLngLat();
                    networkLngLatbran.setName(getStringCellValue(sheet.getRow(i), 0));
                    networkLngLatbran.setLng(jsonArray.get(0).toString());
                    networkLngLatbran.setLat(jsonArray.get(1).toString());
                    networkLngLatDao.insert(networkLngLatbran);
                    //将调用的经纬度度设置到networkDelivery
                    networkPlanning.setCoordinateX(new BigDecimal(jsonArray.get(0).toString()));
                    networkPlanning.setCoordinateY(new BigDecimal(jsonArray.get(1).toString()));
                }
            }else {
                networkPlanning.setCoordinateX(new BigDecimal(getStringCellValue(sheet.getRow(i), 1).toString()));
                networkPlanning.setCoordinateY(new BigDecimal(getStringCellValue(sheet.getRow(i), 2).toString()));
            }
            networkPlanning.setName(getStringCellValue(sheet.getRow(i), 0));
            networkPlanning.setOrderQuantity(new BigDecimal(getIntCellValue(sheet.getRow(i), 3)));
            networkPlanning.setType(1);
            networkPlanningList.add(networkPlanning);
        }
        return networkPlanningList;
    }
    //获取二级配送中心的sheet
    private List<NetworkPlanning> secondaryDelivery(HSSFWorkbook workbook) {
        List<NetworkPlanning> networkPlanningList = new ArrayList<NetworkPlanning>();
        HSSFSheet sheet = workbook.getSheetAt(1);
        int lastRowNum = sheet.getLastRowNum();
        NetworkPlanning networkPlanning;
        for (int i = 1; i <= lastRowNum; i++) {
            sheet.getRow(i);
            networkPlanning = new NetworkPlanning();
            if(getStringCellValue(sheet.getRow(i), 1)==null||getStringCellValue(sheet.getRow(i), 1)==""||getStringCellValue(sheet.getRow(i), 2)==null||getStringCellValue(sheet.getRow(i), 2)==""){
                //调用数据库获取到对应的经纬度
                EntityWrapper<NetworkLngLat> wrapperBranch = new EntityWrapper<NetworkLngLat>();
                wrapperBranch.eq("name", getStringCellValue(sheet.getRow(i), 0));
                List<Map<String, Object>> networkLngLatBranch=networkLngLatDao.selectMaps(wrapperBranch);
                //如果获取到了，则设置对应的经纬度
                if (networkLngLatBranch.size()>0){
                    networkPlanning.setCoordinateX(new BigDecimal(networkLngLatBranch.get(0).get("lng").toString()));
                    networkPlanning.setCoordinateY(new BigDecimal(networkLngLatBranch.get(0).get("lat").toString()));
                }else{
                    //如果为获取到，调用百度地图，将该地址保存到数据库networkLngLatbran
                    JSONArray jsonArray=latlng(getStringCellValue(sheet.getRow(i), 0));
                    NetworkLngLat networkLngLatbran=new NetworkLngLat();
                    networkLngLatbran.setName(getStringCellValue(sheet.getRow(i), 0));
                    networkLngLatbran.setLng(jsonArray.get(0).toString());
                    networkLngLatbran.setLat(jsonArray.get(1).toString());
                    networkLngLatDao.insert(networkLngLatbran);
                    //将调用的经纬度度设置到networkDelivery
                    networkPlanning.setCoordinateX(new BigDecimal(jsonArray.get(0).toString()));
                    networkPlanning.setCoordinateY(new BigDecimal(jsonArray.get(1).toString()));
                }
            }else {
                networkPlanning.setCoordinateX(new BigDecimal(getStringCellValue(sheet.getRow(i), 1)));
                networkPlanning.setCoordinateY(new BigDecimal(getStringCellValue(sheet.getRow(i), 2)));
            }
            networkPlanning.setName(getStringCellValue(sheet.getRow(i), 0));
            networkPlanning.setOrderQuantity(new BigDecimal(getIntCellValue(sheet.getRow(i), 3)));
            networkPlanning.setBatchName(getStringCellValue(sheet.getRow(i), 4));
            networkPlanning.setType(2);
            networkPlanningList.add(networkPlanning);
        }
        return networkPlanningList;
    }

    //通过地址名来获取到经纬度,调用百度地图来处理的
    private JSONArray latlng(String placename){
        JSONArray jsonArray=new JSONArray();
        String url = String.format("http://api.map.baidu.com/geocoder?address=%s&output=json&key=%s", URLEncoder.encode(placename.toString()),"NURx5GUAQ1IBZVBMCQSpYppRsDdTpbxG");
        CloseableHttpClient client = HttpClients.createDefault();
        double lng=0;
        double lat=0;
        try {
            HttpGet httpGet = new HttpGet(url);
            HttpResponse httpResponse = client.execute(httpGet);
            if (httpResponse.getStatusLine().getStatusCode() == 200) {
                HttpEntity httpEntity = httpResponse.getEntity();
                String text = EntityUtils.toString(httpEntity);//取出应答字符串
                JSONObject json = JSONObject.parseObject(text);
                System.out.println("查询   "+placename);
                lat = json.getJSONObject("result").getJSONObject("location").getDouble("lng");
                lng = json.getJSONObject("result").getJSONObject("location").getDouble("lat");
                jsonArray.add(lat);
                jsonArray.add(lng);
                return jsonArray;
            }
            jsonArray.add("");
            jsonArray.add("");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return jsonArray;
    }
}
